package com.oracle.util;

import com.oracle.vo.BjVo;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;

public class BjExcelUtil {
    /**
     * 导出
     * @param request
     * @param response
     * @param execlName
     * @param dataList
     * @throws Exception
     */
    public void exportExcel(HttpServletRequest request, HttpServletResponse response, String execlName, List dataList) throws Exception {

        response.setCharacterEncoding("utf-8");

        // 获取下载路劲
//        String path = request.getSession().getServletContext().getRealPath("/upload/");
        String path = "d:/upload/";
        //生成的PDF文档名称
        String fileName = execlName + ".xlsx";

        //易于扩展
        //如果文件名中包含order 调用创建orderexecl 的方法 例：orderexcel
        //创建Excel
        createTaskExcel(path + fileName, dataList);

        File file = new File(path + fileName); // 根据文件路径获得File文件

        response.setContentType("application/x-xls;charset=GBK");
        // 文件名
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + new String(fileName.getBytes(), "ISO8859-1") + "\"");

        response.setContentLength((int) file.length());
        byte[] buffer = new byte[4096];// 缓冲区
        BufferedOutputStream output = null;
        BufferedInputStream input = null;
        try {
            output = new BufferedOutputStream(response.getOutputStream());
            input = new BufferedInputStream(new FileInputStream(file));
            int n = -1;
            // 遍历，开始下载
            while ((n = input.read(buffer, 0, 4096)) > -1) {
                output.write(buffer, 0, n);
            }
            output.flush();
            response.flushBuffer();
        } catch (Exception e) {
            // 异常自己捕捉
        } finally {
            // 关闭流，不可少
            if (input != null)
                input.close();
            if (output != null)
                output.close();
        }
    }

    public void createTaskExcel(String dest, List<BjVo> dataList) throws IOException {
        //1、创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //2、新建工作表
        XSSFSheet sheet = workbook.createSheet("20240102");

        //--------------------------- 样式(选做) -------------------------------
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();

        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        //--------------------------- 样式(选做) -------------------------------

        packTaskData(sheet, style, dataList);

        //6、保存工作簿
        FileOutputStream fileout = new FileOutputStream(dest);
        workbook.write(fileout);
        System.out.println("创建2007格式的Excel成功");
    }

    private void packTaskData(XSSFSheet sheet, XSSFCellStyle style, List<BjVo> datalist) {

        String[] headers = {"ID", "用户名", "姓名", "电话"};
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //3、新增行
        for (int i = 0; i < datalist.size(); i++) {
            row = sheet.createRow(i + 1);

            row.createCell(0).setCellValue(datalist.get(i).getGlyId());
            row.createCell(1).setCellValue(datalist.get(i).getGlyUsername());
            row.createCell(2).setCellValue(datalist.get(i).getGlyRealname());
            row.createCell(3).setCellValue(datalist.get(i).getGlyPhone());
        }
    }
}
